﻿using ECommon;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace EOrm
{
    /// <summary>
    /// 数据库拓展类
    /// </summary>
    public static class DbExtension
    {

        /// <summary>
        /// 设定据库服务提供者库文件目录
        /// </summary>
        /// <param name="db">数据库实例</param>
        /// <param name="libDir">数据库服务提供者库文件目录</param>
        /// <returns>数据库实例</returns>
        /// <exception cref="ArgumentNullException">变量为空异常</exception>
        /// <exception cref="DirectoryNotFoundException">数据库服务提供者库文件目录不存在引发的异常</exception>
        public static Db SetDbProviderLibDir(this Db db, string libDir)
        {
            ValidDb(db);
            if (libDir == null || string.IsNullOrEmpty(libDir))
            {
                throw new ArgumentNullException("libDir", "要设定的据库服务提供者目录为空");
            }
            if (!Directory.Exists(libDir))
            {
                throw new DirectoryNotFoundException("要设定的据库服务提供者目录不存在");
            }
            if (!libDir.EndsWith("\\"))
            {
                libDir = libDir + "\\";
            }
            db.ProviderLibDir = libDir;
            return db;
        }

        /// <summary>
        /// 设定据库服务提供者库文件名称
        /// </summary>
        /// <param name="db">数据库实例</param>
        /// <param name="libName">数据库服务提供者库文件名</param>
        /// <returns>数据库实例</returns>
        /// <exception cref="ArgumentNullException">变量为空异常</exception>
        public static Db SetDbProviderLibName(this Db db, string libName)
        {
            ValidDb(db);
            if (libName == null || string.IsNullOrEmpty(libName))
            {
                throw new ArgumentNullException("libName", "要设定的数据库服务提供者库文件名为空");
            }
            db.ProviderLibName = libName;
            return db;
        }

        /// <summary>
        /// 获取数据库服务提供者库文件全路径
        /// </summary>
        /// <param name="db">数据库实例</param>
        /// <param name="libName">数据库服务提供者库文件名</param>
        /// <returns>数据库服务提供者库文件全路径</returns>
        public static string GetDbProviderLibPath(this Db db)
        {
            ValidDb(db);
            return db.ProviderLibPath;
        }

        /// <summary>
        /// 设置数据库启用分割线命名法
        /// </summary>
        /// <param name="db">数据库</param>
        /// <returns>数据库</returns>
        public static Db SetDbSeperated(this Db db)
        {
            return SetDbSeperated(db, true);
        }

        /// <summary>
        /// 设置数据库是否启用分割线命名法
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="isSeperated">数据库是否启用分割线命名法</param>
        /// <returns>数据库</returns>
        public static Db SetDbSeperated(this Db db, bool isSeperated)
        {
            ValidDb(db);
            if (db.Tables != null && db.Tables.Count > 0)
            {
                UpdateTableSeperated(db.Tables, isSeperated);
            }
            return db;
        }

        /// <summary>
        /// 绑定配置类库中的数据表
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="assemblyPath">配置类库</param>
        /// <returns>数据库</returns>
        /// <exception cref="FileNotFoundException">未找到文件异样</exception>
        public static Database BindTableByAssembly(this Database db, string assemblyPath)
        {
            return BindTableByAssembly(db, assemblyPath, true);
        }

        /// <summary>
        /// 绑定配置类库中的数据表
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="assemblyPath">配置类库</param>
        /// <param name="isSeperated">是否启用分割线命名法</param>
        /// <returns>数据库</returns>
        /// <exception cref="FileNotFoundException">未找到文件异样</exception>
        public static Database BindTableByAssembly(this Database db, string assemblyPath, bool isSeperated)
        {
            ValidDb(db);
            if (!File.Exists(assemblyPath))
            {
                throw new FileNotFoundException(string.Format("路径：{0}的文件不存在", assemblyPath));
            }
            List<TableBuilder> tables = OrmProviderLoader.GetChildClasses<TableBuilder>(assemblyPath);
            ITableFactory tableFactory = OrmProviderLoader.GetInterface<ITableFactory>(db.DbInstance.ProviderLibPath);
            if (tables != null)
            {
                foreach (TableBuilder tbl in tables)
                {
                    ITable table = tableFactory.Create(tbl.TableType, isSeperated);
                    if (table != null)
                    {
                        tbl.Build(table);
                        if (db.DbInstance.Tables.Exists(a => a.Name == table.Name))
                        {
                            db.DbInstance.Tables.Remove(db.DbInstance.Tables.Find(b => b.Name == table.Name));
                        }
                        db.DbInstance.Tables.Add(table);
                    }
                }
            }
            return db;
        }
        /// <summary>
        /// 执行数据库操作
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="sql">sql语句</param>
        /// <returns>受影响的数据记录数</returns>
        public static int ForInt(this Database db, string sql)
        {
            int res = 0;
            IDbConnection conn = db.DbInstance.DbCon;
            using (IDbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                ActiveDbCon(db.DbInstance);
                res=cmd.ExecuteNonQuery();
            }
            SqlCreator.AddHistorySql(sql);
            return res;
        }

        /// <summary>
        /// 执行数据库记录查询
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="sql">sql语句</param>
        /// <returns>记录数据表</returns>
        public static DataTable ForDataTable(this Database db, string sql)
        {
            DataSet ds = new DataSet();
            IDbConnection conn = db.DbInstance.DbCon;
            ActiveDbCon(db.DbInstance);
            using (IDbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                IDbAdapterCreator adapterCreator = OrmProviderLoader.GetInterface<IDbAdapterCreator>(db.DbInstance.ProviderLibPath);
                IDbDataAdapter adapter = adapterCreator.Create(cmd);
                adapter.Fill(ds);
            }
            SqlCreator.AddHistorySql(sql);
            if (ds==null||ds.Tables.Count<=0)
            {
                return new DataTable();
            }
            else
            {
                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 执行数据库对象查询
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="sql">sql语句</param>
        /// <returns>查询得到的对象</returns>
        public static object ForObj(this Database db, string sql)
        {
            object res = null;
            IDbConnection conn = db.DbInstance.DbCon;
            using (IDbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                ActiveDbCon(db.DbInstance);
                res=cmd.ExecuteScalar();
            }
            SqlCreator.AddHistorySql(sql);
            return res;
        }


        /// <summary>
        /// 删除数据库实体
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="entity">要删除的实体</param>
        /// <returns>受影响的数据记录数</returns>
        public static int DeleteOne<T>(this Database db, T entity) where T : class
        {
            ValidDb(db);
            //ValidEntity(entity);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Delete_"+table.Name+"_"+ (entity==null ? "0" : entity.GetHashCode().ToString());
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sql = SqlCreator.AddCacheSql(sqlKey, sqlCreator.DeleteOne(table, entity));
            }
            return ForInt(db, sql);
        }
        /// <summary>
        /// 删除数据库实体列表
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="entities">实体列表</param>
        /// <returns>受影响的数据记录数</returns>
        public static int DeleteList<T>(this Database db, List<T> entities) where T : class
        {
            ValidDb(db);
            //ValidEntity(entities);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Delete_"+table.Name+"_"+ (entities==null||entities.Count<=0 ? "0" : entities.GetHashCode().ToString());
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sql =SqlCreator.AddCacheSql(sqlKey, sqlCreator.DeleteList(table, entities));
            }
            return ForInt(db, sql);
        }
        /// <summary>
        /// 根据条件删除数据库记录
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="where">条件表达式</param>
        /// <returns>受影响的数据记录数</returns>
        public static int Delete<T>(this Database db, Expression<Func<T, bool>> where) where T : class
        {
            ValidDb(db);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Delete_"+table.Name+"_"+ (where==null ? "0" : where.GetHashCode().ToString());
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sql = SqlCreator.AddCacheSql(sqlKey, sqlCreator.Delete(table, where));
            }
            return ForInt(db, sql);
        }

        /// <summary>
        /// 更新数据库实体
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="entity">要更新的实体</param>
        /// <returns>受影响的数据记录数</returns>
        public static int UpdateOne<T>(this Database db, T entity) where T : class
        {
            ValidDb(db);
            //Valid(entity);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Update_"+table.Name+"_"+ (entity==null ? "0" : entity.GetHashCode().ToString());
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sql = SqlCreator.AddCacheSql(sqlKey, sqlCreator.Update(table, entity));
            }
            return ForInt(db, sql);
        }

        /// <summary>
        /// 更新数据库实体列表
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="entities">实体列表</param>
        /// <returns>受影响的数据记录数</returns>
        public static int UpdateList<T>(this Database db, List<T> entities) where T : class
        {
            ValidDb(db);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Update_"+table.Name+"_"+ (entities==null||entities.Count<=0 ? "0" : entities.GetHashCode().ToString());
            List<string> sqls = new List<string>();
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sqls.AddRange(SqlCreator.GetSqlCache(sqlKey).Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries).Select(a => a +";").ToList());
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sqls.AddRange(entities.Select(a => sqlCreator.Update(table, a)).ToList());
            }
            int res = 0;
            sqls.ForEach(a =>
            {
                int tmp = ForInt(db, a);
                if (tmp > 0)
                {
                    res=res+tmp;
                }
            });
            if (res>0)
            {
                SqlCreator.AddCacheSql(sqlKey, string.Join("", sqls.ToArray()));
            }
            return res;
        }

        /// <summary>
        /// 添加数据库实体
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="entity">要添加的数据库实体</param>
        /// <returns>受影响的数据记录数</returns>
        public static int InsertOne<T>(this Database db, T entity) where T : class
        {
            ValidDb(db);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Insert_"+table.Name+"_"+ (entity==null ? "0" : entity.GetHashCode().ToString());
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sql = SqlCreator.AddCacheSql(sqlKey, sqlCreator.Insert(table, entity));
            }

            return ForInt(db, sql);
        }
        /// <summary>
        ///  添加数据库实体列表
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="entities">要添加的数据库实体列表</param>
        /// <returns>受影响的数据记录数</returns>
        public static int InsertList<T>(this Database db, List<T> entities) where T : class
        {
            ValidDb(db);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Insert_"+table.Name+"_"+ (entities==null||entities.Count<=0 ? "0" : entities.GetHashCode().ToString());
            List<string> sqls = new List<string>();
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sqls.AddRange(SqlCreator.GetSqlCache(sqlKey).Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries).Select(a => a +";").ToList());
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sqls.AddRange(entities.Select(a => sqlCreator.Insert(table, a)).ToList());
            }
            int res = 0;
            sqls.ForEach(a =>
            {
                int tmp = ForInt(db, a);
                if (tmp > 0)
                {
                    res=res+tmp;
                }
            });
            if (res>0)
            {
                SqlCreator.AddCacheSql(sqlKey, string.Join("", sqls.ToArray()));
            }
            return res;
        }
        /// <summary>
        /// 添加单一实体并返回主键
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="entity">要添加的实体</param>
        /// <returns>主键</returns>
        public static object InsertReturnKey<T>(this Database db, T entity) where T : class
        {
            ValidDb(db);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "InsertReturnKey_"+table.Name+"_"+ (entity==null ? "0" : entity.GetHashCode().ToString());
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sql = SqlCreator.AddCacheSql(sqlKey, sqlCreator.InsertReturnKey(table, entity));
            }
            return ForObj(db, sql);
        }

        /// <summary>
        /// 根据条件查询实体列表
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <returns>实体列表</returns>
        public static T SelectOne<T>(this Database db) where T : class
        {
            List<T> tmp = SelectList<T, int>(db, null, null, 1);
            return tmp==null||tmp.Count<=0 ? null : tmp[0];
            //ValidDb(db);
            //ITable table = GetTable<T>(db.DbInstance);
            //string sqlKey = "Select_"+table.Name;
            //string sql = "";
            //if (SqlCreator.ExistKey(sqlKey))
            //{
            //    sql=SqlCreator.GetSql(sqlKey);
            //}
            //else
            //{
            //    SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
            //    sql = sqlCreator.Select(table);
            //}
            //DataTable dt = ForDataTable(db, sql);
            //List<T> list = ConvertDataTableToList<T>(dt, table);
            //return list;
        }

        /// <summary>
        /// 根据条件查询实体列表
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="where">查询条件</param>
        /// <returns>实体列表</returns>
        public static T SelectOne<T>(this Database db, Expression<Func<T, bool>> where) where T : class
        {
            List<T> tmp = SelectList<T, int>(db, where, null, 1);
            return tmp==null||tmp.Count<=0 ? null : tmp[0];
            //ValidDb(db);
            //ITable table = GetTable<T>(db.DbInstance);
            //string sqlKey = "Select_"+table.Name+"_"+ where==null?"0":where.GetHashCode().ToString();
            //string sql = "";
            //if (SqlCreator.ExistKey(sqlKey))
            //{
            //    sql=SqlCreator.GetSql(sqlKey);
            //}
            //else
            //{
            //    SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
            //    sql = sqlCreator.Select(table, where);
            //}
            //DataTable dt = ForDataTable(db, sql);
            //List<T> list = ConvertDataTableToList<T>(dt, table);
            //return list;
        }

        /// <summary>
        /// 根据条件查询实体列表
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="where">查询条件</param>
        /// <param name="orderBy">排序条件</param>
        /// <returns>实体列表</returns>
        public static T SelectOne<T, U>(this Database db, Expression<Func<T, bool>> where, Expression<Func<T, U>> orderBy) where T : class
        {
            List<T> tmp = SelectList<T, U>(db, where, orderBy, 1);
            return tmp==null||tmp.Count<=0 ? null : tmp[0];
        }

        /// <summary>
        /// 根据条件查询实体列表
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <returns>实体列表</returns>
        public static List<T> SelectList<T>(this Database db) where T : class
        {
            return SelectList<T, int>(db, null, null, 0);
            //ValidDb(db);
            //ITable table = GetTable<T>(db.DbInstance);
            //string sqlKey = "Select_"+table.Name;
            //string sql = "";
            //if (SqlCreator.ExistKey(sqlKey))
            //{
            //    sql=SqlCreator.GetSql(sqlKey);
            //}
            //else
            //{
            //    SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
            //    sql = sqlCreator.Select(table);
            //}
            //DataTable dt = ForDataTable(db, sql);
            //List<T> list = ConvertDataTableToList<T>(dt, table);
            //return list;
        }

        /// <summary>
        /// 根据条件查询实体列表
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="where">查询条件</param>
        /// <returns>实体列表</returns>
        public static List<T> SelectList<T>(this Database db, Expression<Func<T, bool>> where) where T : class
        {
            return SelectList<T, int>(db, where, null, 0);
            //ValidDb(db);
            //ITable table = GetTable<T>(db.DbInstance);
            //string sqlKey = "Select_"+table.Name+"_"+ where.GetHashCode().ToString();
            //string sql = "";
            //if (SqlCreator.ExistKey(sqlKey))
            //{
            //    sql=SqlCreator.GetSql(sqlKey);
            //}
            //else
            //{
            //    SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
            //    sql = sqlCreator.Select(table, where);
            //}
            //DataTable dt = ForDataTable(db, sql);
            //List<T> list = ConvertDataTableToList<T>(dt, table);
            //return list;
        }

        /// <summary>
        /// 根据条件查询实体列表
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="where">查询条件</param>
        /// <param name="orderBy">排序条件</param>
        /// <returns>实体列表</returns>
        public static List<T> SelectList<T, U>(this Database db, Expression<Func<T, bool>> where, Expression<Func<T, U>> orderBy) where T : class
        {
            return SelectList(db, where, orderBy, 0);
            //ValidDb(db);
            //ITable table = GetTable<T>(db.DbInstance);
            //string sqlKey = "Select_"+table.Name+"_"+ where.GetHashCode().ToString()+"_"+orderBy.GetHashCode().ToString();
            //string sql = "";
            //if (SqlCreator.ExistKey(sqlKey))
            //{
            //    sql=SqlCreator.GetSql(sqlKey);
            //}
            //else
            //{
            //    SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
            //    sql = sqlCreator.Select(table, where, orderBy);
            //}
            //DataTable dt = ForDataTable(db, sql);
            //List<T> list = ConvertDataTableToList<T>(dt, table);
            //return list;
        }

        /// <summary>
        /// 根据条件查询实体列表
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <typeparam name="U">排序泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="where">查询条件</param>
        /// <param name="orderBy">排序条件</param>
        /// <param name="limit">行数限制</param>
        /// <returns>实体列表</returns>
        public static List<T> SelectList<T, U>(this Database db, Expression<Func<T, bool>> where, Expression<Func<T, U>> orderBy, int limit) where T : class
        {
            ValidDb(db);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Select_"+table.Name+"_"+ (where==null ? "0" : where.GetHashCode().ToString())+"_"+(orderBy==null ? "0" : orderBy.GetHashCode().ToString())+"_"+limit.ToString();
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sql = SqlCreator.AddCacheSql(sqlKey, sqlCreator.Select(table, where, orderBy, limit));
            }
            DataTable dt = ForDataTable(db, sql);
            List<T> list = ConvertDataTableToList<T>(dt, table);
            return list;
        }
        /// <summary>
        /// 获取总计结果
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="where">总计条件</param>
        /// <returns>总计结果</returns>
        public static int Count<T>(this Database db, Expression<Func<T, bool>> where) where T : class
        {
            ValidDb(db);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Count_"+table.Name+"_"+ (where==null ? "0" : where.GetHashCode().ToString());
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sql = SqlCreator.AddCacheSql(sqlKey, sqlCreator.Count(table, where));
            }
            object obj = ForObj(db, sql);
            return Convert.ToInt32(obj);
        }
        /// <summary>
        /// 获取总计结果
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <returns>总计结果</returns>
        public static int Count<T>(this Database db) where T : class
        {
            ValidDb(db);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Count_"+table.Name;
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                sql = SqlCreator.AddCacheSql(sqlKey, sqlCreator.Count<T>(table, null));
            }
            object obj = ForObj(db, sql);
            return Convert.ToInt32(obj);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="total">分页统计</param>
        /// <param name="pageIndex">页序号</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="where">分页条件表达式</param>
        /// <param name="orderBy">分页排序表达式</param>
        /// <returns>分页数据表</returns>
        public static DataTable PageDateTable<T, U>(this Database db, out int total, int pageIndex = 1, int pageSize = 10, Expression<Func<T, bool>> where = null, Expression<Func<T, U>> orderBy = null) where T : class
        {
            total=Count(db, where);
            ITable table = GetTable<T>(db.DbInstance);
            string sqlKey = "Page"+table.Name+"_"+ (where==null ? "0" : where.GetHashCode().ToString())+"_"+(orderBy==null ? "0" : orderBy.GetHashCode().ToString())+"_"+pageIndex.ToString()+"_"+pageSize.ToString();
            string sql = "";
            if (SqlCreator.CacheContainsKey(sqlKey))
            {
                sql=SqlCreator.GetSqlCache(sqlKey);
            }
            else
            {
                SqlCreator sqlCreator = OrmProviderLoader.GetChildClass<SqlCreator>(db.DbInstance.ProviderLibPath);
                int limit = pageSize;
                int offset = pageSize * (pageIndex - 1);
                sql = SqlCreator.AddCacheSql(sqlKey, sqlCreator.Page(table, where, orderBy, limit, offset));
            }
            return ForDataTable(db, sql);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <param name="total">分页统计</param>
        /// <param name="pageIndex">页序号</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="where">分页条件表达式</param>
        /// <param name="orderBy">分页排序表达式</param>
        /// <returns>分页数据列表</returns>
        public static List<T> PageList<T, U>(this Database db, out int total, int pageIndex = 1, int pageSize = 10, Expression<Func<T, bool>> where = null, Expression<Func<T, U>> orderBy = null) where T : class
        {
            DataTable dt = PageDateTable(db, out total, pageIndex, pageSize, where, orderBy);
            ITable table = GetTable<T>(db.DbInstance);
            return ConvertDataTableToList<T>(dt, table);
        }

        /// <summary>
        /// 验证数据库
        /// </summary>
        /// <param name="db">要验证的数据库</param>
        /// <exception cref="ArgumentNullException">变量为空异常</exception>
        internal static void ValidDb(Database db)
        {
            if (db == null||db.DbInstance==null)
            {
                throw new ArgumentNullException("db", "数据库不可为空");
            }
        }

        /// <summary>
        /// 验证数据库
        /// </summary>
        /// <param name="db">要验证的数据库</param>
        /// <exception cref="ArgumentNullException">变量为空异常</exception>
        internal static void ValidDb(Db db)
        {
            if (db == null)
            {
                throw new ArgumentNullException("db", "数据库不可为空");
            }
        }

        /// <summary>
        /// 更新数据表是否启用分割线命名法
        /// </summary>
        /// <param name="tables">要更新的数据表</param>
        /// <param name="isSeperated">是否启用分割线命名法</param>
        internal static void UpdateTableSeperated(List<ITable> tables, bool isSeperated)
        {
            if (tables != null && tables.Count > 0)
            {
                tables.ForEach(a =>
                {
                    if (a != null)
                    {
                        a.IsSeperated = isSeperated;
                        a.DbName = isSeperated ? StringTool.ToSeparator(a.DbName) : StringTool.ToPascal(a.DbName);
                        UpdateFieldSeperated(a.Fields, isSeperated);
                    }
                });
            }
        }

        /// <summary>
        /// 更新字段是否启用分割线命名法
        /// </summary>
        /// <param name="fields">要更新的字段</param>
        /// <param name="isSeperated">是否启用分割线命名法</param>
        internal static void UpdateFieldSeperated(List<IField> fields, bool isSeperated)
        {
            if (fields != null && fields.Count > 0)
            {
                fields.ForEach(a =>
                {
                    if (a != null)
                    {
                        a.IsSeperated = isSeperated;
                        a.DbName = isSeperated ? StringTool.ToSeparator(a.DbName) : StringTool.ToPascal(a.DbName);
                    }
                });
            }
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <typeparam name="T">目标数据表泛型</typeparam>
        /// <param name="db">数据库</param>
        /// <returns>数据表</returns>
        /// <exception cref="ArgumentException">未发现数据表异常</exception>
        internal static ITable GetTable<T>(Db db) where T : class
        {
            string tblName = typeof(T).Name;
            ITable table = db.Tables.Find(a => a.Name == typeof(T).Name);
            if (table == null)
            {
                throw new ArgumentException(string.Format("未找到数据库表:{0}的绑定", tblName), "table");
            }
            return table;
        }

        /// <summary>
        /// 数据库构建缓存
        /// </summary>
        internal static Dictionary<string, TableBuilder> _dbBuilderCache = new Dictionary<string, TableBuilder>();

        /// <summary>
        /// 激活数据库连接
        /// </summary>
        /// <param name="db">数据库</param>
        /// <returns>激活的数据库连接</returns>
        /// <exception cref="ArgumentNullException">参数为空异常</exception>
        /// <exception cref="Exception">数据库忙碌异常</exception>
        private static void ActiveDbCon(Db db)
        {
            switch (db.DbCon.State)
            {
                case ConnectionState.Connecting:
                case ConnectionState.Executing:
                case ConnectionState.Fetching:
                    throw new Exception("数据库连接忙碌中，无法执行新任务");
                case ConnectionState.Closed:
                    db.DbCon.Open();
                    break;
                case ConnectionState.Broken:
                    db.DbCon.Close();
                    db.DbCon.Open();
                    break;
                case ConnectionState.Open:
                default:
                    break;
            }
        }

        /// <summary>
        /// 将数据表转换为实体集合
        /// </summary>
        /// <typeparam name="T">实体反省</typeparam>
        /// <param name="dt">数据表</param>
        /// <param name="table">数据表配置</param>
        /// <returns>实体集合</returns>
        /// <exception cref="ArgumentNullException">参数为空异常</exception>
        /// <exception cref="Exception">非空字段赋空值异常</exception>
        private static List<T> ConvertDataTableToList<T>(DataTable dt, ITable table) where T : class
        {
            if (dt==null||dt.Rows.Count<=0)
            {
                return new List<T>();
                //throw new ArgumentNullException("dt", "数据表为空异常");
            }
            if (table==null||table.Fields.Count<=0)
            {
                throw new ArgumentNullException("table", "数据表配置为空异常");
            }
            List<T> res = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                T tmp = Activator.CreateInstance<T>();
                foreach (IField field in table.Fields)
                {
                    PropertyInfo piField = ReflectionTool.GetPropertyInfo(tmp, field.Name);
                    object val = row[field.DbName];
                    if (val==null||val==DBNull.Value)
                    {
                        if (field.IsNullable)
                        {
                            piField.SetValue(tmp, null, null);
                        }
                        else
                        {
                            throw new Exception(string.Format("数据库属性列：{0}是否非空配置错误，对应的实体属性为：{1}", field.DbName, field.Name));
                        }
                    }
                    else
                    {
                        piField.SetValue(tmp, val, null);
                    }
                }
                res.Add(tmp);
            }
            return res;
        }
    }
}
